import sqlite3


# 创建表
def create_table():
    conn = sqlite3.connect('data.db')
    cursor = conn.cursor()
    # 创建用户表
    sql1 = '''create table user(
    username varchar(200),
    password varchar(200)
    )'''
    # 创建棋局表
    sql2 = '''create table chess(
    id integer primary key autoincrement,
    chess_num int,
    username varchar(200),
    chess_index int,
    step int,
    color int
    )'''

    cursor.execute(sql1)
    cursor.execute(sql2)
    cursor.close()
    conn.close()
    print('新建用户表')


# 删除表
def drop_table():
    conn = sqlite3.connect('data.db')
    cursor = conn.cursor()
    sql1 = '''drop table user'''
    sql2 = '''drop table chess'''

    cursor.execute(sql1)
    cursor.execute(sql2)
    cursor.close()
    conn.close()
    print('删除用户表')


# 新建用户
def insert_user(username, password):
    conn = sqlite3.connect('data.db')
    cursor = conn.cursor()
    sql = '''insert into
    user(username, password)
    values (?, ?)'''

    cursor.execute(sql, (username, password))
    conn.commit()
    cursor.close()
    conn.close()


# 删除用户
def delete_user(username):
    conn = sqlite3.connect('data.db')
    cursor = conn.cursor()
    sql = "delete from user where username = ?"

    cursor.execute(sql, (username,))
    conn.commit()
    cursor.close()
    conn.close()


# 查询用户
def select_user(username):
    conn = sqlite3.connect('data.db')
    cursor = conn.cursor()
    sql = "select password from user where username = ?"

    result = cursor.execute(sql, (username,))
    result = result.fetchone()
    cursor.close()
    conn.close()
    return result


# 插入棋局
def insert_chess(chessNum, username, chessIndex, step, color):
    conn = sqlite3.connect('data.db')
    cursor = conn.cursor()
    sql = '''insert into
        chess(chess_num, username, chess_index, step, color)
        values (?, ?, ?, ?, ?)'''

    cursor.execute(sql, (chessNum, username, chessIndex, step, color))
    conn.commit()
    cursor.close()
    conn.close()


# 获取某局信息
def select_chess(chessNum):
    conn = sqlite3.connect('data.db')
    cursor = conn.cursor()
    sql = "select chess_index, step, color from chess where chess_num = ?"
    result = cursor.execute(sql, (chessNum,))

    result = result.fetchall()
    cursor.close()
    conn.close()
    return result


# 获取全部棋局
def get_chess():
    conn = sqlite3.connect('data.db')
    cursor = conn.cursor()
    sql = "select chess_num, username from chess"
    result = cursor.execute(sql)
    result = result.fetchall()
    cursor.close()
    conn.close()
    return result
